package org.odk.collect.android.activities;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Locale;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import org.odk.collect.android.R;
import org.odk.collect.android.application.Collect;

import android.app.Activity;
import android.app.ListActivity;
import android.content.Context;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.os.Environment;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;

public class prefillXlsChooser extends ListActivity {
	TextView tv;
	ListView mList;
	ArrayList<String> fileList = new ArrayList<String>();
	String extra;
	File sdCard = Environment.getExternalStorageDirectory();
	String j;
	String aFinal="";
	public static Workbook w=null;
	String errorMsg="";
	public void onCreate(Bundle savedInstanceState) {
		//	ApplicationMonitor monitor= new ApplicationMonitor(this);
		//  	monitor.markAsResourceIntensive(3);


		super.onCreate(savedInstanceState);
		setContentView(R.layout.prefill_xlschooser);
		Intent intent = getIntent();
		extra = intent.getStringExtra("extra");
		// tv= (TextView)findViewById(R.id.choosexls);
		// mList = (ListView)findViewById(R.id.xlsList);

		SQLiteOpenHelper mDbHelper = null;
		SQLiteDatabase mDb1 = null;
		mDbHelper = new SQLiteOpenHelper(Collect.METADATA_PATH, "forms.db", null);
		mDb1 = mDbHelper.getReadableDatabase(); 

		String [] displayName = new String[1];
		Cursor c =mDb1.query("forms", null	, "_id" + "='" + extra + "'", null, null, null, null);

		if (c != null ) {
			if  (c.moveToFirst()) {
				j = c.getString(c.getColumnIndex("displayName"));
				j=j.replace(".", " ");
			}   
		}
		mDb1.close();
		// -------------------changing here----------
		File mf = new File(sdCard.getAbsolutePath() + "/odk/forms/"+j+"-media/");
		ListDir(mf);
		/* String[] values = new String[] { "Android", "iPhone", "WindowsMobile",
	                "Blackberry", "WebOS", "Ubuntu", "Windows7", "Max OS X",
	                "Linux", "OS/2" };*/

		ArrayAdapter<String> adapter = new ArrayAdapter<String>(this,
				android.R.layout.simple_list_item_1, fileList);
		setListAdapter(adapter);


	}
	public void ListDir(File f){
		File[] files = f.listFiles();
		fileList.clear();
		for (File file : files){
			int lIndex = file.getPath().lastIndexOf("."); 
			int tLen = file.getPath().length();
			if(file.getPath().substring(lIndex+1,tLen ).equalsIgnoreCase("xls"))
			{
				int lIndex1 = file.getPath().lastIndexOf("/");
				fileList.add(file.getPath().substring(lIndex1+1,tLen));
			}
		}
	}

	public void onListItemClick(ListView l, View v, int position, long id) {

		String erMsg=validateXls(fileList.get(position));
		//x.cancel();
		if(erMsg.equalsIgnoreCase(""))
		{
			Toast.makeText(this, "XLS validated", Toast.LENGTH_LONG).show();
			
			Intent i = new Intent(prefillXlsChooser.this.getApplicationContext(), Prefiller.class);
			i.putExtra("name",fileList.get(position));
			i.putExtra("extra", extra);
			i.putExtra("string1", aFinal);
			startActivity(i);
			finish();
		}
		else
		{
		Toast.makeText(this, "There was an error in the xls,file not validated ,check error file", Toast.LENGTH_LONG).show();
			
			Intent i = new Intent(getApplicationContext(), MainMenuActivity.class);
			startActivity(i);
		}

	}
	public String validateXls(String xlsFile)
	{
		Toast.makeText(getApplicationContext(), "Please wait validating data source xls", Toast.LENGTH_LONG).show();

		File dir1 = new File(sdCard.getAbsolutePath() + "/odk/forms/"+xlsFile+".xls");


		File txtFile = new File(sdCard.getAbsolutePath() + "/odk/forms/"+j+".txt");
		if(txtFile.exists())
		{
			FileInputStream fIn = null;
			try {
				fIn = new FileInputStream(txtFile);
			} catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				System.out.println("problem in reading file");

			}
			BufferedReader myReader = null;
			try {
				myReader = new BufferedReader(new InputStreamReader(fIn));
			} catch (Exception e) {
				System.out.println("exception caught");
			}
			System.out.println("fine till here");
			String aDataRow = "";
			String aBuffer = "";

			try {
				while ((aDataRow = myReader.readLine()) != null) {
					//System.out.println(aDataRow);
					aBuffer += aDataRow + "\n";
				}
			} catch (IOException e) {
				// TODO Auto-generated catch block
				System.out.println("definitely problem here");
			}
			aFinal=aFinal+aBuffer;

		}
		else
		{

			File myFile= new File(sdCard.getAbsolutePath() + "/odk/forms/"+j+".xml");
			//			File myFile = new File(full_file);"

			System.out.println("fine");
			// tv1.setText(" no prob");

			FileInputStream fIn = null;
			try {
				fIn = new FileInputStream(myFile);
			} catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				System.out.println("problem in reading file");

			}
			BufferedReader myReader = null;
			try {
				myReader = new BufferedReader(new InputStreamReader(fIn));
			} catch (Exception e) {
				System.out.println("exception caught");
			}
			System.out.println("fine till here");
			String aDataRow = "";
			String aBuffer = "";

			try {
				while ((aDataRow = myReader.readLine()) != null) {
					//System.out.println(aDataRow);
					aBuffer += aDataRow + "\n";
				}
			} catch (IOException e) {
				// TODO Auto-generated catch block
				System.out.println("definitely problem here");
			}
			//	int start_head = aBuffer.indexOf("<head_family/>");
			//	int end_head = aBuffer.indexOf("</head_family>");
			/*	String head=sheet.getCell(1,i).getContents();
			String panchayat =sheet.getCell(10,i).getContents();
			String village =sheet.getCell(8,i).getContents();
			String jobId=sheet.getCell(0,i).getContents();
			int male=Integer.parseInt(sheet.getCell(6,i).getContents());
			int female=Integer.parseInt(sheet.getCell(7,i).getContents());

			int disabled=Integer.parseInt(sheet.getCell(4,i).getContents());

			System.out.println(head);
			System.out.println(panchayat);
			System.out.println(village);

			int shg=Integer.parseInt(sheet.getCell(5,i).getContents());*/
			aBuffer=aBuffer.replaceAll("\\n", "");
			aBuffer=aBuffer.replaceAll("\\s{2,}", "");
			int st=aBuffer.indexOf("<instance>");
			int end = aBuffer.indexOf("</instance>");
			aFinal=aFinal+aBuffer.substring(0, 21);
			aFinal=aFinal+aBuffer.substring(st+10, end);
			while(aFinal.indexOf("jr:")!=-1)
			{
				int ind = aFinal.indexOf("jr:");
				System.out.println("----------------"+ind);
				int q=ind;
				while((aFinal.charAt(ind)!='<'))
				{
					ind--;
					System.out.println("In loop:"+ind);
				}
				String sub1 = aFinal.substring(ind+1, q-1);
				System.out.println("substring: "+sub1);
				String sub2 =  "</"+sub1+">";
				int st1 = ind;
				int end1 = aFinal.indexOf(sub2); 
				String superSub = aFinal.substring(st1, end1+sub2.length());
				aFinal= aFinal.replace(superSub, "");
			}
			// x.cancel();
			try {
				txtFile.createNewFile();
			} catch (IOException e1) {
				// TODO Auto-generated catch block

				e1.printStackTrace();
			}
			FileWriter fw = null;
			try {
				fw = new FileWriter(txtFile.getAbsoluteFile());
			} catch (IOException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			BufferedWriter myWriter = new BufferedWriter(fw);
			try {
				myWriter.write(aFinal);
				myWriter.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		} 
		File dir10 = new File(sdCard.getAbsolutePath() + "/odk/forms/"+j+"-media/"+xlsFile);
		int flag3=1;
		try {
			try {
				w = Workbook.getWorkbook(dir10);
				//String[] sheets =w.getSheetNames();
				//System.out.println("sheets names are"+ sheets);
				//String smg=sheets[0];
				//String smg1=sheets[1];
				//String smg2=sheets[2];
				//	System.out.println("sheets 0 is" + smg + " "+smg1+" "+smg2 );

				if(w.getSheets().length!=3)
				{
					errorMsg=errorMsg + "3 sheets not found \n";
					flag3=0;
				}
				/*else if(!(sheets[0].equalsIgnoreCase("data")&&sheets[1].equalsIgnoreCase("prefilling")&&sheets[2].equalsIgnoreCase("filter")))
					{
						errorMsg=errorMsg + "improper sheets names \n";
					}*/
			} catch (BiffException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if(flag3==1)
		{
			Sheet sheet0 = w.getSheet(0);
			Sheet sheet1 = w.getSheet(1);
			Sheet sheet2 = w.getSheet(2);
			if(sheet1.getColumns()!=2)
			{
				errorMsg=errorMsg+"prefilling sheet does not have 2 sheets";
			}
			else if(sheet2.getColumns()!=2)
			{
				errorMsg=errorMsg+"filter sheet does not have 2 sheets";
			}
			else
			{
				for(int i=0;i<sheet1.getRows();i++)
				{
					Cell cell0 = sheet1.getCell(0, i);
					Cell cell1 = sheet1.getCell(1, i);
					if(cell0.getContents().equals("")||cell0.getContents().equals(null))
					{
						errorMsg = errorMsg + " row "+i+ " of the excel name is empty \n";
					}
					if(cell1.getContents().equals("")|| cell1.getContents().equals(null))
					{
						errorMsg = errorMsg + " row "+i+ " of the form name is empty \n";
					}
				}
				for(int i=1;i<sheet1.getRows();i++)
				{
					int flag=1;
					Cell cell1 = sheet1.getCell(0, i);
					if(!(cell1.getContents().equals("")|| cell1.getContents().equals(null)))
					{
						for(int j=0; j<sheet0.getColumns();j++)
						{

							Cell cell0 = sheet0.getCell(j, 0);
							if(cell1.getContents().equalsIgnoreCase(cell0.getContents()))
							{
								flag=0;
								break;
							}

						}
						if(flag==1)
						{
							errorMsg = errorMsg + " could not find "+ cell1.getContents()+"in the Data sheet\n";
						}
					}
				}
				System.out.println("aFinal is :"+aFinal);
				for(int i=1;i<sheet1.getRows();i++)
				{
					int flag=1;
					Cell cell1 = sheet1.getCell(1, i);
					if(!(cell1.getContents().equals("")|| cell1.getContents().equals(null)))
					{
						System.out.println("Content is :"+cell1.getContents());
						if(aFinal.indexOf(cell1.getContents())==-1)
						{
							flag=0;
						}
						if(flag==0)
						{
							errorMsg = errorMsg + " could not find "+ cell1.getContents()+" of prefilling sheet in the form \n";
						}
					}
				}
				for(int i=1;i<sheet2.getRows();i++)
				{
					Cell cell0 = sheet2.getCell(0, i);
					Cell cell1 = sheet2.getCell(1, i);
					if(cell0.getContents().equals("")||cell0.getContents().equals(null))
					{
						errorMsg = errorMsg + " row "+i+ " of the excel name in filter sheet is empty \n";
					}
					if(cell1.getContents().equals("")|| cell1.getContents().equals(null))
					{
						errorMsg = errorMsg + " row "+i+ " of the question in filter sheet is empty \n";
					}
				}
				for(int i=1;i<sheet2.getRows();i++)
				{
					int flag=1;
					Cell cell2 = sheet2.getCell(0, i);
					if(!(cell2.getContents().equals("")|| cell2.getContents().equals(null)))
					{
						for(int j=0; j<sheet0.getColumns();j++)
						{

							Cell cell0 = sheet0.getCell(j, 0);
							if(cell2.getContents().equalsIgnoreCase(cell0.getContents()))
							{
								flag=0;
								break;
							}

						}
						if(flag==1)
						{
							errorMsg = errorMsg + " could not find "+ cell2.getContents()+" of filter sheet in the Data sheet\n";
						}
					}
				}
			}
		}
		System.out.println("errormsg is :"+errorMsg);
		if(!errorMsg.equalsIgnoreCase(""))
		{
			File errlog = new File(sdCard.getAbsolutePath() + "/odk/forms/"+j+"-media/errorLog.txt");
			if(!errlog.exists())
			{
				try {
					errlog.createNewFile();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			FileWriter fw1 = null;
			try {
				fw1 = new FileWriter(errlog.getAbsoluteFile());
			} catch (IOException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			BufferedWriter myWriter1 = new BufferedWriter(fw1);
			try {
				myWriter1.append("Error in validation of "+xlsFile+"\n");
				myWriter1.append(errorMsg+"\n");
				String time = new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss", Locale.ENGLISH).format(Calendar.getInstance()
						.getTime());
				myWriter1.append("---------------"+time+"------------");

				myWriter1.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}


		}
		return errorMsg;
	}
}